#!/usr/bin/env python
# coding=utf-8
# __author__ = 'Yunchao Ling'

import MySQLdb
from pymongo import MongoClient
import sys

default_encoding = 'utf-8'
if sys.getdefaultencoding() != default_encoding:
    reload(sys)
    sys.setdefaultencoding(default_encoding)


# 载入出版社
def LoadPublisher():
    infile = open("D:/publisherStat_sorted_withaddress.txt", "r")
    ht = {}
    for line in infile:
        line = line.rstrip("\n")
        splitline = line.split("\t")
        ht[splitline[0]] = splitline[2]
    infile.close()
    return ht


# 载入出版社
def LoadCaseSensitivePublisher():
    infile = open("D:/case_sensitive_publisher.txt", "r")
    ht = {}
    for line in infile:
        line = line.rstrip("\n")
        ht[line] = 1
    infile.close()
    return ht


# 连接MySQL
def InitMySQL():
    # MYSQL_DATABASE_NAME = 'plosp'
    # MYSQL_HOST = '10.188.188.22'
    # MYSQL_PORT = '3306'
    # MYSQL_USER_NAME = 'plosp'
    # MYSQL_PASSWORD = 'plosp'
    # MYSQL_CHAR_SET = 'utf8'
    MYSQL_DATABASE_NAME = 'plosp'
    MYSQL_HOST = '10.188.188.22'
    MYSQL_PORT = 3306
    MYSQL_USER_NAME = 'plosp'
    MYSQL_PASSWORD = 'plosp'
    MYSQL_CHAR_SET = 'utf8'
    conn = MySQLdb.connect(host=MYSQL_HOST, user=MYSQL_USER_NAME, passwd=MYSQL_PASSWORD, port=MYSQL_PORT,
                           db=MYSQL_DATABASE_NAME, charset=MYSQL_CHAR_SET)
    return conn


def CloseMySQL(MySQL_Connection):
    MySQL_Connection.close()


# 连接MongoDB
def InitMongoDB():
    MONGODB_HOST = '10.188.188.100'
    MONGODB_PORT = 27017
    MONGODB_DB_NAME = 'journal'
    MONGODB_COLLECTION_NAME = 'journal'
    connection = MongoClient(MONGODB_HOST, MONGODB_PORT)
    db = connection[MONGODB_DB_NAME]
    collection = db[MONGODB_COLLECTION_NAME]
    return connection, collection


def CloseMongoDB(MongoDB_Connection):
    MongoDB_Connection.close()


# 查找和判断出版社
def DeterminePublisher(MongoDB_Collection, ISSN_P, ISSN_E, NlmId, ISOAbbr, FullTitle):
    publisher_casesensitive_ht = LoadCaseSensitivePublisher()
    query = []
    if ISSN_P != None and ISSN_P != "":
        query.append({"ISSNSet": ISSN_P})
    if ISSN_E != None and ISSN_E != "":
        query.append({"ISSNSet": ISSN_E})
    if NlmId != None and NlmId != "":
        query.append({"NlmId": NlmId})
    if ISOAbbr != None and ISOAbbr != "":
        query.append({"ISOAbbr": ISOAbbr})
    if FullTitle != None and FullTitle != "":
        query.append({"FullTitle": FullTitle})
    if len(query) > 0:
        publisher = ""
        result_count = MongoDB_Collection.count({"$or": query})
        if result_count == 0:
            publisher = ""
        elif result_count == 1:
            result_unique = MongoDB_Collection.find_one({"$or": query})
            if result_unique.has_key("UniquePublisher"):
                publisher = result_unique["UniquePublisher"]
            else:
                publisher = ""
        elif result_count > 1:
            ht = {}
            result_multiple = MongoDB_Collection.find({"$or": query})
            for result in result_multiple:
                if result.has_key("UniquePublisher"):
                    if ht.has_key(result["UniquePublisher"]):
                        ht[result["UniquePublisher"]] += 1
                    else:
                        ht[result["UniquePublisher"]] = 1
            if len(ht) == 0:
                publisher = ""
            elif len(ht) == 1:
                publisher = ht.keys()[0]
            elif len(ht) > 1:
                ht_sorted = sorted(ht.iteritems(), key=lambda d: d[1], reverse=True)
                publisher = ht_sorted[0][0]
        if publisher == "":
            return None
        else:
            if publisher_casesensitive_ht.has_key(publisher.upper()):
                publisher = publisher.upper()
            return publisher
    else:
        return None


# 找到MySQL Journal表中每条期刊记录在MongoDB所对应的出版社，并且将出版社名称计数排序
# 生成的文件第1列是出版社名，第2列是出版社对应期刊数。
def PublisherStatistic(MySQL_Connection, MongoDB_Collection):
    cursor = MySQL_Connection.cursor()
    sql = "SELECT publisher_id,issn_print,issn_electronic,Unique_Nlm_ID,isoabbreviation,title FROM tb_dds_journal"
    cursor.execute(sql)

    publisherStat = {}
    for result in cursor.fetchall():
        publisher = DeterminePublisher(MongoDB_Collection, result[1], result[2], result[3], result[4], result[5])
        if publisher != None:
            if publisherStat.has_key(publisher):
                publisherStat[publisher] += 1
            else:
                publisherStat[publisher] = 1
    cursor.close()
    publisherStat_sorted = sorted(publisherStat.iteritems(), key=lambda d: d[1], reverse=True)
    outfile = open("D:/publisherStat_sorted.txt", "w")
    for item in publisherStat_sorted:
        outfile.write(item[0] + "\t" + str(item[1]) + "\n")
        outfile.flush()
    outfile.close()


# 从出版社计数排序文件得到相对的出版社地址
def AddPublisherAddress(MongoDB_Collection):
    infile = open("D:/publisherStat_sorted.txt", "r")
    outfile = open("D:/publisherStat_sorted_withaddress.txt", "w")
    for line in infile:
        line = line.rstrip()
        publisher = line.split("\t")[0]
        results = MongoDB_Collection.find({"UniquePublisher": publisher})
        ht = {}
        for result in results:
            if result.has_key("PublisherAddress"):
                if ht.has_key(result["PublisherAddress"][0]):
                    ht[result["PublisherAddress"][0]] += 1
                else:
                    ht[result["PublisherAddress"][0]] = 1
        if len(ht) == 0:
            outfile.write(line + "\t\n")
        elif len(ht) == 1:
            outfile.write(line + "\t" + ht.keys()[0] + "\n")
        elif len(ht) > 1:
            ht_sorted = sorted(ht.iteritems(), key=lambda d: d[1], reverse=True)
            outfile.write(line + "\t" + ht_sorted[0][0] + "\n")
        outfile.flush()
    infile.close()
    outfile.close()


# 补充MySQL的Publisher表
def UpdatePublisher(MySQL_Connection, MongoDB_Collection):
    publisher_ht = LoadPublisher()

    cursor1 = MySQL_Connection.cursor()
    SQL_GetJournal = "SELECT publisher_id,issn_print,issn_electronic,Unique_Nlm_ID,isoabbreviation,title,id FROM tb_dds_journal"
    cursor1.execute(SQL_GetJournal)
    for result in cursor1.fetchall():
        publisher = DeterminePublisher(MongoDB_Collection, result[1], result[2], result[3], result[4], result[5])
        journal_id = str(result[6])
        if publisher != None:
            print "=================================================="
            print "journal_id: " + journal_id + "; publisher: " + publisher
            publisher_id = ""
            cursor2 = MySQL_Connection.cursor()
            SQL_LookupPublisher = "SELECT id FROM tb_dds_publisher WHERE name=%s"
            Params_LookupPublisher = (publisher,)
            cursor2.execute(SQL_LookupPublisher, Params_LookupPublisher)
            result_LookupPublisher = cursor2.fetchone()
            if result_LookupPublisher != None:
                print "Update publisher process"
                publisher_id = str(result_LookupPublisher[0])
                cursor3 = MySQL_Connection.cursor()
                SQL_UpdatePublisher = "UPDATE tb_dds_publisher SET name=%s,ioc=%s WHERE id=%s"
                Params_UpdatePublisher = (publisher, publisher_ht[publisher], publisher_id)
                cursor3.execute(SQL_UpdatePublisher, Params_UpdatePublisher)
                MySQL_Connection.commit()
                cursor3.close()
            else:
                print "Insert publisher process"
                cursor4 = MySQL_Connection.cursor()
                SQL_InsertPublisher = "INSERT INTO tb_dds_publisher(name,ioc,update_time) values(%s,%s,%s)"
                Params_InsertPublisher = (publisher, publisher_ht[publisher], "2016-07-21 18:00:00")
                cursor4.execute(SQL_InsertPublisher, Params_InsertPublisher)
                MySQL_Connection.commit()
                cursor4.close()
                cursor5 = MySQL_Connection.cursor()
                cursor5.execute(SQL_LookupPublisher, Params_LookupPublisher)
                result_Publisher = cursor5.fetchone()
                publisher_id = str(result_Publisher[0])
                cursor5.close()
            cursor2.close()
            print "Update journal process"
            cursor6 = MySQL_Connection.cursor()
            SQL_UpdateJournal = "UPDATE tb_dds_journal SET publisher_id=%s WHERE id=%s"
            Params_UpdateJournal = (publisher_id, journal_id)
            cursor6.execute(SQL_UpdateJournal, Params_UpdateJournal)
            MySQL_Connection.commit()
            cursor6.close()
    cursor1.close()


# 测试Publisher表name唯一性
def TestPublisherUnique(MySQL_Connection):
    cursor = MySQL_Connection.cursor()
    sql = "SELECT name from tb_dds_publisher"
    cursor.execute(sql)
    ht = {}
    for result in cursor.fetchall():
        if ht.has_key(result[0]):
            ht[result[0]] += 1
            print result[0]
        else:
            ht[result[0]] = 1
    cursor.close()


# 测试Publisher和地址列表中大小写重复性
def TestPublisherAddressCase():
    infile = open("D:/publisherStat_sorted_withaddress.txt", "r")
    ht = {}
    for line in infile:
        line = line.rstrip("\n")
        splitline = line.split("\t")
        publisher = splitline[0].upper()
        if ht.has_key(publisher):
            ht[publisher] += 1
        else:
            ht[publisher] = 1
    infile.close()
    for key in ht.keys():
        if ht[key] > 1:
            print key


if __name__ == "__main__":
    MySQL_Connection = InitMySQL()
    MongoDB_Connection, MongoDB_Collection = InitMongoDB()

    UpdatePublisher(MySQL_Connection, MongoDB_Collection)
    # TestPublisherUnique(MySQL_Connection)

    CloseMySQL(MySQL_Connection)
    CloseMongoDB(MongoDB_Connection)
